//
//  ViewController.swift
//  Calculator
//
//  Created by 白瑾浩 on 2022/1/12.
//

import UIKit
import SnapKit
import SwiftyJSON
import SQLite
import SQLite3

class SQLViewController: UIViewController {
    
    override func viewDidLoad() {
        super.viewDidLoad()
        self.view.backgroundColor = .blue
        print( "SQLViewController" )
        let board = BoardView()
        
        
        
        view.addSubview( board )
        
        board.snp.makeConstraints { make in
            make.left.right.bottom.equalTo( 0 )
            make.height.equalTo( self.view ).multipliedBy( 5/8.0 )
        }
        
        do {
            // Do any additional setup after loading the view.
            let filePath = NSSearchPathForDirectoriesInDomains(FileManager.SearchPathDirectory.documentDirectory, FileManager.SearchPathDomainMask.userDomainMask, true).last!
                print(filePath)
                let file = filePath + "/test.sqlite"
            
            let db = try Connection(file)
            
            let users = Table("users")
            let id = Expression<Int64>("id")
            let name = Expression<String?>("name")
            let email = Expression<String>("email")
            
            try db.run(users.create { t in
                t.column(id, primaryKey: true)
                t.column(name)
                t.column(email, unique: true)
            })
            // CREATE TABLE "users" (
                //     "id" INTEGER PRIMARY KEY NOT NULL,
                //     "name" TEXT,
                //     "email" TEXT NOT NULL UNIQUE
                // )

                let insert = users.insert(name <- "Alice", email <- "alice@mac.com")
                let rowid = try db.run(insert)
                // INSERT INTO "users" ("name", "email") VALUES ('Alice', 'alice@mac.com')

                for user in try db.prepare(users) {
                    print("id: \(user[id]), name: \(user[name]), email: \(user[email])")
                    // id: 1, name: Optional("Alice"), email: alice@mac.com
                }
                // SELECT * FROM "users"

                let alice = users.filter(id == rowid)

                try db.run(alice.update(email <- email.replace("mac.com", with: "me.com")))
                // UPDATE "users" SET "email" = replace("email", 'mac.com', 'me.com')
                // WHERE ("id" = 1)

                try db.run(alice.delete())
                // DELETE FROM "users" WHERE ("id" = 1)

                try db.scalar(users.count) // 0
                // SELECT count(*) FROM "users"
        }catch{
            print("======\(error)" )
        }
    }
    
    
}

